package com.mapper;

import com.pojo.Adept_lfy;
import com.pojo.Grade_lfy;
import com.pojo.Zixunshi_lfy;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;
@Mapper
public interface Zixunshi_lfyMapper {

    @Select("SELECT z.zixunshi_id,z.zixunshi_name, z.zixunshi_introduce, z.zixunshi_photo, z.zixunshi_gid, " +
            " g.grade_name " +
            "FROM zixunshi_lfy z " +
            "LEFT JOIN grade_lfy g ON z.zixunshi_gid = g.grade_id LIMIT #{limit}")
    @Results({
            @Result(property = "zixunshiName", column = "zixunshi_name"),
            @Result(property = "zixunshiIntroduce", column = "zixunshi_introduce"),
            @Result(property = "zixunshiPhoto", column = "zixunshi_photo"),
            @Result(property = "zixunshiGid", column = "zixunshi_gid"),
            @Result(property = "grade_lfy",  column = "zixunshi_gid", one = @One(select = "com.mapper" +
                    ".Zixunshi_lfyMapper.selectNameById",fetchType = FetchType.LAZY))
        })
    List<Zixunshi_lfy> selectName(@Param("limit") Integer limit);

    @Select("select grade_name from grade_lfy where grade_id=#{id}")
    List<Grade_lfy> selectNameById(Integer id);

    @Select("select zixunshi_photo,zixunshi_address,zixunshi_name,grade_name ,zixunshi_gid from zixunshi_lfy  left " +
            "join grade_lfy  " +
            "on " +
            "zixunshi_gid = grade_id where zixunshi_id=#{id} ")
    @Results({
                @Result(property = "zixunshiPhoto", column = "zixunshi_photo"),
            @Result(property = "zixunshiAddress", column = "zixunshi_address"),
            @Result(property = "zixunshiGid", column = "zixunshi_gid"),
            @Result(property = "grade_lfy",  column = "zixunshi_gid", one = @One(select = "com.mapper" +
                    ".Zixunshi_lfyMapper.selectNameById",fetchType = FetchType.LAZY))
    })
    Zixunshi_lfy selectCityByName(Integer id);

    @Select("select zixunshi_id,shiadept_zid,shiadept_aid from zixunshi_lfy left " +
            "join " +
            "shiadept_lfy on " +
            "zixunshi_id=shiadept_zid " +
            "left join adept_lfy on shiadept_aid=adept_id where zixunshi_id=#{id}")
    @Results({
            @Result(property = "zixunshiName", column = "zixunshi_name"),
            @Result(property = "adeptLfyList", column = "zixunshi_id", many = @Many(select = "com.mapper" +
                    ".Zixunshi_lfyMapper.selectAdeptById",fetchType = FetchType.LAZY))
    })
    List<Zixunshi_lfy> selectAdept(Integer id);

    @Select("select  adept_name from adept_lfy left join shiadept_lfy on adept_id=shiadept_aid where " +
            "shiadept_zid=#{id}")
    List<Adept_lfy> selectAdeptById(Integer id);

    @Select("select zixunshi_id,zixunshi_photo,zixunshi_address,zixunshi_name,grade_name ,zixunshi_gid from " +
            "zixunshi_lfy  " +
            "left join grade_lfy on zixunshi_gid = grade_id left join shiadept_lfy on zixunshi_id=shiadept_zid left " +
            "join adept_lfy on shiadept_aid=adept_id where grade_name=#{gradeName}")
    @Results({
            @Result(property = "zixunshiId", column = "zixunshi_id" ,id=true) ,
            @Result(column = "zixunshi_name",property = "zixunshiName"),
            @Result(property = "zixunshiPhoto", column = "zixunshi_photo"),
            @Result(property = "zixunshiAddress", column = "zixunshi_address"),
            @Result(property = "zixunshiGid", column = "zixunshi_gid"),
            @Result(property = "grade_lfy",  column = "zixunshi_gid", one = @One(select = "com.mapper" +
                    ".Zixunshi_lfyMapper.selectNameById",fetchType = FetchType.LAZY)),
            @Result(property = "adeptLfyList", column = "zixunshi_id", many = @Many(select = "com.mapper" +
                    ".Zixunshi_lfyMapper.selectAdeptById",fetchType = FetchType.LAZY))
    })
    List<Zixunshi_lfy> selectAll(@Param("gradeName") String gradeName);


    List<Zixunshi_lfy> selectAll1(@Param("gradeName") String gradeName,
                                  @Param("address") String address,
                                  @Param("sex") String sex,
                                  @Param("type") List<String> type,
                                  @Param("adeptName") List<String> adeptName);


    @Select("select zixunshi_lfy.*" +
            " from " +
            "zixunshi_lfy left join grade_lfy on zixunshi_gid=grade_id  left join shiadept_lfy " +
            "on zixunshi_id=shiadept_zid left join adept_lfy on shiadept_aid=adept_id")
    @Results({
            @Result(property = "zixunshiId", column = "zixunshi_id" ,id=true) ,
            @Result(column = "zixunshi_name",property = "zixunshiName"),
            @Result(property = "zixunshiPhoto", column = "zixunshi_photo"),
            @Result(property = "zixunshiAddress", column = "zixunshi_address"),
            @Result(property = "zixunshiSex" , column = "zixunshi_sex"),
            @Result(property = "zixunshiGid", column = "zixunshi_gid"),
            @Result(property = "zixunshiIntroduce", column = "zixunshi_introduce"),
            @Result(property = "grade_lfy",  column = "zixunshi_gid", one = @One(select = "com.mapper" +
                    ".Zixunshi_lfyMapper.selectNameById",fetchType = FetchType.EAGER)),
            @Result(property = "adeptLfyList", column = "zixunshi_id", many = @Many(select = "com.mapper" +
                    ".Zixunshi_lfyMapper.selectAdeptById",fetchType = FetchType.EAGER))
    })
    List<Zixunshi_lfy> searchAll();

    @Select("select zixunshi_id,zixunshi_photo,zixunshi_address,zixunshi_name,grade_name ,zixunshi_gid from " +
            "zixunshi_lfy  " +
            "left join grade_lfy on zixunshi_gid = grade_id left join shiadept_lfy on zixunshi_id=shiadept_zid left " +
            "join adept_lfy on shiadept_aid=adept_id where zixunshi_name like concat ('%',#{zixunshiName},'%')")
    @Results({
            @Result(property = "zixunshiId", column = "zixunshi_id" ,id=true) ,
            @Result(column = "zixunshi_name",property = "zixunshiName"),
            @Result(property = "zixunshiPhoto", column = "zixunshi_photo"),
            @Result(property = "zixunshiAddress", column = "zixunshi_address"),
            @Result(property = "zixunshiGid", column = "zixunshi_gid"),
            @Result(property = "grade_lfy",  column = "zixunshi_gid", one = @One(select = "com.mapper" +
                    ".Zixunshi_lfyMapper.selectNameById",fetchType = FetchType.EAGER)),
            @Result(property = "adeptLfyList", column = "zixunshi_id", many = @Many(select = "com.mapper" +
                    ".Zixunshi_lfyMapper.selectAdeptById",fetchType = FetchType.EAGER))
    })
    List<Zixunshi_lfy> selectAllByName(@Param("zixunshiName") String zixunshiName);
    @Delete("delete  from zixunshi_lfy where zixunshi_id=#{zixunshiId}")
    Integer delete(@Param("zixunshiId") Integer zixunshiId);

    @Insert("insert into zixunshi_lfy(zixunshi_name,zixunshi_gid,zixunshi_sex,zixunshi_address,zixunshi_introduce )" +
            "values (#{zixunshiName},#{zixunshiGid},#{zixunshiSex},#{zixunshiAddress},#{zixunshiIntroduce}" +
            ")")
    int insertInfo(@Param("zixunshiName") String zixunshiName,@Param("zixunshiGid") Integer zixunshiGid,@Param(
            "zixunshiSex") String zixunshiSex,@Param("zixunshiAddress") String zixunshiAddress,@Param(
                    "zixunshiIntroduce" ) String zixunshiIntroduce);

    @Insert("insert into shiadept_lfy(shiadept_zid,shiadept_aid) values (#{shiadeptZid},#{shiadeptAid})")
    int insertAdept(@Param("shiadeptZid") Integer shiadeptZid,@Param("shiadeptAid") Integer shiadeptAid);

    @Update("update zixunshi_lfy set zixunshi_name=#{zixunshiName},zixunshi_gid=#{zixunshiGid}," +
            "zixunshi_sex=#{zixunshiSex},zixunshi_address=#{zixunshiName},zixunshi_introduce=#{zixunshiIntroduce}," +
            "zixunshi_del=#{zixunshiDel} where zixunshi_id=#{zixunshiId}")
    int updateInfo(@Param("zixunshiName") String zixunshiName,@Param("zixunshiGid") Integer zixunshiGid,@Param(
            "zixunshiSex") String zixunshiSex,@Param("zixunshiAddress") String zixunshiAddress,@Param(
                    "zixunshiIntroduce") String zixunshiIntroduce, @Param("zixunshiDel") Integer zixunshiDel,@Param(
                            "zixunshiId") Integer zixunshiId);
}
